﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Configuration;
using System.Text;
using CsharpTrainer.Linq.Model;

namespace CsharpTrainer.Linq.Service.Northwind
{
    public class EmployeeOrderService
    {
        public IList<EmployeeOrder> GetEmployeeOrdersByTitle(string title)
        {
            
            DataContext context = new DataContext(ConfigurationManager.ConnectionStrings["NorthwindConnStr"].ToString());
       
            NorthwindDBDataContext northwindContext = new NorthwindDBDataContext();
                    
            var query = (from emp in northwindContext.Employees
                            join order in northwindContext.Orders
                            on emp.EmployeeID equals order.EmployeeID
                            where emp.Title == title
                            orderby order.OrderDate descending
                            select new EmployeeOrder
                            {
                                LastName = emp.LastName,
                                FirstName = emp.FirstName,
                                Title = emp.Title,
                                OrderID = order.OrderID,
                                OrderDate = (DateTime)order.OrderDate
                            });

            return query.ToList<EmployeeOrder>();       
        }
    }
}
